/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package diet.dao;

import diet.dto.UserDTO;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Vrety
 */
public class UserDAO {

    //Author: CAMPK
    public boolean add(UserDTO user) {
        Connection con = null;
        PreparedStatement stm = null;
        try {
            con = DAO.ConnectWithDriver();
            stm = con.prepareStatement("INSERT INTO [User](email,password,role) VALUES(?,?,?)");
            stm.setString(1, user.getEmail());
            stm.setString(2, user.getPassword());
            stm.setString(3, user.getRole());
            stm.executeUpdate();
            return true;
        } catch (SQLException ex) {
            System.out.println("SQLException Error: " + ex.getMessage());
            return false;
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    System.out.println("Connection error: " + ex.getMessage());
                }
            } else if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException ex) {
                    System.out.println("Statement error " + ex.getMessage());
                }
            }
        }
    }

    public boolean delete(int id) {
        Connection con = null;
        PreparedStatement stm = null;
        try {
            con = DAO.ConnectWithDriver();
            stm = con.prepareStatement("DELETE FROM [User] WHERE UserID=? ");
            stm.setInt(1, id);
            stm.executeUpdate();
            return true;
        } catch (SQLException ex) {
            System.out.println("SQLException Error: " + ex.getMessage());
            return false;
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    System.out.println("Connection error: " + ex.getMessage());
                }
            } else if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException ex) {
                    System.out.println("Statement error " + ex.getMessage());
                }
            }
        }
    }

    public boolean update(UserDTO user) {
        Connection con = null;
        PreparedStatement stm = null;
        try {
            con = DAO.ConnectWithDriver();
            stm = con.prepareStatement("UPDATE [User] SET Name=?, Password=?,Male=?,Birthday=?,Phone=?,Height=?,Weight=? "
                    + " WHERE UserID=?");
            stm.setString(1, user.getName());
            stm.setString(2, user.getPassword());
            stm.setInt(3, user.getMale());
            stm.setDate(4, (Date) user.getBirthday());
            stm.setInt(5, user.getPhone());
            stm.setInt(6, user.getHeight());
            stm.setInt(7, user.getWeight());
            stm.setInt(8, user.getUserID());
            stm.executeUpdate();
            return true;
        } catch (SQLException ex) {
            System.out.println("SQLException Error: " + ex.getMessage());
            return false;
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    System.out.println("Connection error: " + ex.getMessage());
                }
            } else if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException ex) {
                    System.out.println("Statement error " + ex.getMessage());
                }
            }
        }
    }

    //User for login
    public UserDTO getUser() {
        return null;
    }

    public ArrayList<UserDTO> show() {
        ArrayList<UserDTO> users = new ArrayList<UserDTO>();
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            con = DAO.ConnectWithDriver();
            stm = con.prepareStatement("SELECT * from [User] WHERE Role = 'Customer' ");
            rs = stm.executeQuery();
            while (rs.next()) {
                UserDTO user = new UserDTO(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getInt(5),
                        rs.getDate(6), rs.getInt(7), rs.getInt(8), rs.getInt(9), rs.getString(10));
                users.add(user);
            }

        } catch (SQLException ex) {
            System.out.println("SQLException: " + ex.getMessage());
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    System.out.println("Connection error: " + ex.getMessage());
                }
            } else if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException ex) {
                    System.out.println("Statement error " + ex.getMessage());
                }
            } else if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    System.out.println("ResultSet error " + ex.getMessage());
                }
            }
        }
        return users;
    }

    public ArrayList<UserDTO> search(String name) {
        ArrayList<UserDTO> users = new ArrayList<UserDTO>();
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            con = DAO.ConnectWithDriver();
            stm = con.prepareStatement("SELECT * from [User] WHERE Name LIKE ?");
            stm.setString(1, "%" + name + "%");
            rs = stm.executeQuery();
            while (rs.next()) {
                UserDTO user = new UserDTO(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getInt(5),
                        rs.getDate(6), rs.getInt(7), rs.getInt(8), rs.getInt(9), rs.getString(10));
                users.add(user);
            }

        } catch (SQLException ex) {
            System.out.println("SQLException: " + ex.getMessage());
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    System.out.println("Connection error: " + ex.getMessage());
                }
            } else if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException ex) {
                    System.out.println("Statement error " + ex.getMessage());
                }
            } else if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    System.out.println("ResultSet error " + ex.getMessage());
                }
            }
        }
        return users;
    }
    //NhuNHU
    //get UserID by username
    public UserDTO getUserByUsername(String username){
        Connection con = null;
        PreparedStatement stm = null;
        UserDTO userDTO = new UserDTO();
        try {
            con = DAO.ConnectWithDriver();
            stm = con.prepareStatement("SELECT * FROM [User] WHERE Email = ?");
            stm.setString(1, username);
            ResultSet rs = stm.executeQuery();

            if(rs.next()){
                userDTO.setUserID(rs.getInt("UserID"));
            }
            rs.close();
            con.close();
            return userDTO;
        }catch (SQLException ex){
            System.out.println("Statement error " + ex.getMessage());
            return null;
        }
    }
}
